from typing import List, Optional

from sqlalchemy import DateTime, ForeignKeyConstraint, Index, Integer, String, JSON, LargeBinary, TIMESTAMP, Text, text
from sqlalchemy.dialects.mysql import ENUM, TEXT, VARCHAR
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
import datetime
from .base_table import Base, UserBase


class Ranks(Base):
    __tablename__ = 'ranks'
    __table_args__ = {'comment': '头衔列表'}

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True, comment='头衔id')
    name: Mapped[str] = mapped_column(VARCHAR(100), server_default=text("'头衔'"), comment='头衔的名称')
    style: Mapped[Optional[dict]] = mapped_column(JSON, comment='头衔的样式(待开发)')

    user: Mapped[List['User']] = relationship('User', back_populates='rank')


class SportTypes(Base):
    __tablename__ = 'sport_types'
    __table_args__ = {'comment': '运动的类型'}

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True)
    name: Mapped[str] = mapped_column(VARCHAR(255), comment='运动类型的名称')
    description: Mapped[Optional[str]] = mapped_column(VARCHAR(255), comment='描述')
    bg_img: Mapped[Optional[str]] = mapped_column(Text, comment='背景图案')

    sport_records: Mapped[List['SportRecords']] = relationship('SportRecords', back_populates='type')


class Tags(Base):
    __tablename__ = 'tags'
    __table_args__ = {'comment': '标签列表'}

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True)
    name: Mapped[str] = mapped_column(VARCHAR(255), server_default=text("''"), comment='标签名')
    style: Mapped[Optional[dict]] = mapped_column(JSON, comment='样式(待开发)')


class User(UserBase):
    __tablename__ = 'user'
    __table_args__ = (
        ForeignKeyConstraint(['rank_id'], ['ranks.id'], name='user_ranks_id_fk'),
        Index('user_ranks_id_fk', 'rank_id'),
        {'comment': '用户表'}
    )

    id: Mapped[str] = mapped_column(String(255), primary_key=True, comment='用户id')
    name: Mapped[str] = mapped_column(String(255), comment='用户名称')
    sex: Mapped[str] = mapped_column(String(10), server_default=text("'武装直升机'"), comment='用户性别')
    age: Mapped[int] = mapped_column(Integer, server_default=text("'0'"), comment='用户年龄')
    remarks: Mapped[str] = mapped_column(String(500), server_default=text("''"), comment='用户备注')
    tag_ids: Mapped[str] = mapped_column(String(255), server_default=text("''"), comment='标签ids')
    create_time: Mapped[datetime.datetime] = mapped_column(DateTime, server_default=text('CURRENT_TIMESTAMP'),
                                                           comment='加入时间')
    password: Mapped[Optional[str]] = mapped_column(String(255), server_default=text("''"), comment='用户密码')
    head_img: Mapped[Optional[bytes]] = mapped_column(LargeBinary, comment='用户头像')
    low_head_img: Mapped[Optional[bytes]] = mapped_column(LargeBinary, comment='较低像素的头像文件')
    rank_id: Mapped[Optional[str]] = mapped_column(String(255), comment='用户的头衔id')

    rank: Mapped[Optional['Ranks']] = relationship('Ranks', back_populates='user')
    friend_requests: Mapped[List['FriendRequests']] = relationship('FriendRequests',
                                                                   foreign_keys='[FriendRequests.receiver_id]',
                                                                   back_populates='receiver')
    friend_requests_: Mapped[List['FriendRequests']] = relationship('FriendRequests',
                                                                    foreign_keys='[FriendRequests.sender_id]',
                                                                    back_populates='sender')
    friendships: Mapped[List['Friendships']] = relationship('Friendships', foreign_keys='[Friendships.user_id1]',
                                                            back_populates='user')
    friendships_: Mapped[List['Friendships']] = relationship('Friendships', foreign_keys='[Friendships.user_id2]',
                                                             back_populates='user_')
    sport_records: Mapped[List['SportRecords']] = relationship('SportRecords', back_populates='user')
    posts: Mapped[List['Posts']] = relationship('Posts', back_populates='user')
    comments: Mapped[List['Comments']] = relationship('Comments', back_populates='user')
    favorites: Mapped[List['Favorites']] = relationship('Favorites', back_populates='user')
    likes: Mapped[List['Likes']] = relationship('Likes', back_populates='user')


class FriendRequests(Base):
    __tablename__ = 'friend_requests'
    __table_args__ = (
        ForeignKeyConstraint(['receiver_id'], ['user.id'], ondelete='CASCADE', name='friend_requests_ibfk_2'),
        ForeignKeyConstraint(['sender_id'], ['user.id'], ondelete='CASCADE', name='friend_requests_ibfk_1'),
        Index('receiver_id', 'receiver_id'),
        Index('sender_id', 'sender_id', 'receiver_id', unique=True)
    )

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True)
    sender_id: Mapped[str] = mapped_column(VARCHAR(255))
    receiver_id: Mapped[str] = mapped_column(VARCHAR(255))
    status: Mapped[Optional[str]] = mapped_column(ENUM('pending', 'accepted', 'rejected'), server_default=text("'pending'"))
    create_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))

    receiver: Mapped['User'] = relationship('User', foreign_keys=[receiver_id], back_populates='friend_requests')
    sender: Mapped['User'] = relationship('User', foreign_keys=[sender_id], back_populates='friend_requests_')


class Friendships(Base):
    __tablename__ = 'friendships'
    __table_args__ = (
        ForeignKeyConstraint(['user_id1'], ['user.id'], ondelete='CASCADE', name='friendships_ibfk_1'),
        ForeignKeyConstraint(['user_id2'], ['user.id'], ondelete='CASCADE', name='friendships_ibfk_2'),
        Index('user_id1', 'user_id1', 'user_id2', unique=True),
        Index('user_id2', 'user_id2')
    )

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True)
    user_id1: Mapped[str] = mapped_column(VARCHAR(255))
    user_id2: Mapped[str] = mapped_column(VARCHAR(255))
    created_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))

    user: Mapped['User'] = relationship('User', foreign_keys=[user_id1], back_populates='friendships')
    user_: Mapped['User'] = relationship('User', foreign_keys=[user_id2], back_populates='friendships_')


class SportRecords(Base):
    __tablename__ = 'sport_records'
    __table_args__ = (
        ForeignKeyConstraint(['quote_record_id'], ['sport_records.id'], name='sport_records_sport_records_id_fk'),
        ForeignKeyConstraint(['type_id'], ['sport_types.id'], name='sport_records_sport_types_id_fk'),
        ForeignKeyConstraint(['user_id'], ['user.id'], name='sport_records_user_id_fk'),
        Index('sport_records_sport_records_id_fk', 'quote_record_id'),
        Index('sport_records_sport_types_id_fk', 'type_id'),
        Index('sport_records_user_id_fk', 'user_id'),
        {'comment': '用户的运动记录列表'}
    )

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True, comment='记录的id')
    user_id: Mapped[str] = mapped_column(VARCHAR(255), comment='用户id')
    type_id: Mapped[str] = mapped_column(VARCHAR(255), comment='运动的类型(骑行、徒步、长跑、短跑。。。)')
    create_time: Mapped[datetime.datetime] = mapped_column(DateTime, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
    quote_record_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), comment='借鉴的路径记录的id')
    path: Mapped[Optional[dict]] = mapped_column(JSON, comment='路线')
    name: Mapped[Optional[str]] = mapped_column(VARCHAR(255), comment='路线的名称')
    big_img: Mapped[Optional[bytes]] = mapped_column(LargeBinary, comment='大屏显示的展示图')

    quote_record: Mapped[Optional['SportRecords']] = relationship('SportRecords', remote_side=[id], back_populates='quote_record_reverse')
    quote_record_reverse: Mapped[List['SportRecords']] = relationship('SportRecords', remote_side=[quote_record_id], back_populates='quote_record')
    type: Mapped['SportTypes'] = relationship('SportTypes', back_populates='sport_records')
    user: Mapped['User'] = relationship('User', back_populates='sport_records')
    posts: Mapped[List['Posts']] = relationship('Posts', back_populates='record')


class Posts(Base):
    __tablename__ = 'posts'
    __table_args__ = (
        ForeignKeyConstraint(['record_id'], ['sport_records.id'], name='posts_sport_records_id_fk'),
        ForeignKeyConstraint(['user_id'], ['user.id'], name='posts_user_id_fk'),
        Index('posts_sport_records_id_fk', 'record_id'),
        Index('posts_user_id_fk', 'user_id')
    )

    id: Mapped[str] = mapped_column(String(255), primary_key=True)
    user_id: Mapped[str] = mapped_column(String(255), comment='发帖人')
    title: Mapped[str] = mapped_column(String(255))
    content: Mapped[str] = mapped_column(Text)
    create_time: Mapped[datetime.datetime] = mapped_column(DateTime, server_default=text('CURRENT_TIMESTAMP'))
    update_time: Mapped[datetime.datetime] = mapped_column(DateTime, server_default=text('CURRENT_TIMESTAMP'))
    del_flag: Mapped[str] = mapped_column(String(1), server_default=text("'1'"), comment='删除标记')
    record_id: Mapped[Optional[str]] = mapped_column(String(255), comment='关联的运动记录')
    tags: Mapped[Optional[str]] = mapped_column(Text, comment='标签列表')

    record: Mapped[Optional['SportRecords']] = relationship('SportRecords', back_populates='posts')
    user: Mapped['User'] = relationship('User', back_populates='posts')
    comments: Mapped[List['Comments']] = relationship('Comments', back_populates='post')
    favorites: Mapped[List['Favorites']] = relationship('Favorites', foreign_keys='[Favorites.post_id]',
                                                        back_populates='post')
    favorites_: Mapped[List['Favorites']] = relationship('Favorites', foreign_keys='[Favorites.record_id]',
                                                         back_populates='record')
    likes: Mapped[List['Likes']] = relationship('Likes', back_populates='post')


class Comments(Base):
    __tablename__ = 'comments'
    __table_args__ = (
        ForeignKeyConstraint(['origin_comment_id'], ['comments.id'], name='comments_comments_id_fk_2'),
        ForeignKeyConstraint(['post_id'], ['posts.id'], name='comments_posts_id_fk'),
        ForeignKeyConstraint(['request_comment_id'], ['comments.id'], name='comments_comments_id_fk'),
        ForeignKeyConstraint(['user_id'], ['user.id'], name='comments_user_id_fk'),
        Index('comments_comments_id_fk_2', 'origin_comment_id'),
        Index('comments_posts_id_fk', 'post_id'),
        Index('comments_user_id_fk', 'user_id'),
        Index('comments_user_id_fk_2', 'request_comment_id'),
        {'comment': '评论表'}
    )

    id: Mapped[str] = mapped_column(String(255), primary_key=True)
    user_id: Mapped[str] = mapped_column(String(255))
    post_id: Mapped[str] = mapped_column(String(255), comment='帖子id')
    content: Mapped[str] = mapped_column(Text)
    create_time: Mapped[datetime.datetime] = mapped_column(DateTime, server_default=text('CURRENT_TIMESTAMP'))
    request_comment_id: Mapped[Optional[str]] = mapped_column(String(255), comment='回复的目标评论')
    origin_comment_id: Mapped[Optional[str]] = mapped_column(String(255), comment='根评论id')

    origin_comment: Mapped[Optional['Comments']] = relationship('Comments', remote_side=[id],
                                                                foreign_keys=[origin_comment_id],
                                                                back_populates='origin_comment_reverse')
    origin_comment_reverse: Mapped[List['Comments']] = relationship('Comments', remote_side=[origin_comment_id],
                                                                    foreign_keys=[origin_comment_id],
                                                                    back_populates='origin_comment')
    post: Mapped['Posts'] = relationship('Posts', back_populates='comments')
    request_comment: Mapped[Optional['Comments']] = relationship('Comments', remote_side=[id],
                                                                 foreign_keys=[request_comment_id],
                                                                 back_populates='request_comment_reverse')
    request_comment_reverse: Mapped[List['Comments']] = relationship('Comments', remote_side=[request_comment_id],
                                                                     foreign_keys=[request_comment_id],
                                                                     back_populates='request_comment')
    user: Mapped['User'] = relationship('User', back_populates='comments')
    likes: Mapped[List['Likes']] = relationship('Likes', back_populates='comment')


class Favorites(Base):
    __tablename__ = 'favorites'
    __table_args__ = (
        ForeignKeyConstraint(['post_id'], ['posts.id'], name='favorites_posts_id_fk'),
        ForeignKeyConstraint(['record_id'], ['posts.id'], ondelete='CASCADE', name='favorites_ibfk_2'),
        ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE', name='favorites_ibfk_1'),
        Index('favorites_posts_id_fk', 'post_id'),
        Index('record_id', 'record_id'),
        Index('user_id', 'user_id', 'record_id', unique=True)
    )

    id: Mapped[str] = mapped_column(VARCHAR(255), primary_key=True)
    user_id: Mapped[str] = mapped_column(VARCHAR(255))
    record_id: Mapped[str] = mapped_column(VARCHAR(255), server_default=text("''"))
    post_id: Mapped[str] = mapped_column(VARCHAR(255), server_default=text("''"))
    created_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))

    post: Mapped['Posts'] = relationship('Posts', foreign_keys=[post_id], back_populates='favorites')
    record: Mapped['Posts'] = relationship('Posts', foreign_keys=[record_id], back_populates='favorites_')
    user: Mapped['User'] = relationship('User', back_populates='favorites')


class Likes(Base):
    __tablename__ = 'likes'
    __table_args__ = (
        ForeignKeyConstraint(['comment_id'], ['comments.id'], name='likes_comments_id_fk'),
        ForeignKeyConstraint(['post_id'], ['posts.id'], ondelete='CASCADE', name='likes_ibfk_2'),
        ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE', name='likes_ibfk_1'),
        Index('likes_comments_id_fk', 'comment_id'),
        Index('post_id', 'post_id'),
        Index('user_id', 'user_id', 'post_id', unique=True)
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[str] = mapped_column(String(255))
    post_id: Mapped[Optional[str]] = mapped_column(String(255))
    created_time: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime,
                                                                      server_default=text('CURRENT_TIMESTAMP'))
    comment_id: Mapped[Optional[str]] = mapped_column(String(255), comment='点赞评论')

    comment: Mapped[Optional['Comments']] = relationship('Comments', back_populates='likes')
    post: Mapped[Optional['Posts']] = relationship('Posts', back_populates='likes')
    user: Mapped['User'] = relationship('User', back_populates='likes')

